﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Pr0ject0.Models
{
    public class ReferenceDB
    {
        private string connection;
        private List<ReferenceModel> references;
        public List<ReferenceModel> ReferenceList { get{return new List<ReferenceModel>(references);} }
   
        public ReferenceDB()
        {
            connection = "DB";
            update();
        }

        public ReferenceDB(string connectionString)
        {
            connection = connectionString;
            update();
        }

        public void update()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlCommand cmd = new SqlCommand("SELECT * FROM [Reference]", conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            references = new List<ReferenceModel>();

            while (reader.Read())
            {
                ReferenceModel model = new ReferenceModel();
                model.RefId = Convert.ToInt32(reader["RefId"].ToString());
                model.AppId = Convert.ToInt32(reader["AppId"].ToString());
                try
                {
                    model.name = reader["name"].ToString();
                    model.company = reader["company"].ToString();
                    model.title = reader["title"].ToString();
                    model.phone = Convert.ToInt64(reader["phone"].ToString());
                }
                catch (Exception e)
                {

                }
                model.isCalled = Convert.ToBoolean(reader["isContacted"].ToString());
                references.Add(model);
            }

            reader.Close();
            conn.Close();
        }

        public void Add(List<ReferenceModel> list)
        {
            foreach (ReferenceModel model in list)
            {
                Add(model);
            }
        }

        public void Add(ReferenceModel model)
        {
            string insert;
            if (model.name != null && model.name != "")
            {
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
                insert = string.Format("INSERT INTO Reference VALUES((Select Max(RefID) From Reference) + 1, '{0}', '{1}', '{2}', '{3}', 0, '{4}')",
                    model.name, model.company, model.title, model.phone, model.AppId);
                SqlCommand cmd = new SqlCommand(insert, conn);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                update();
            }
        }

        public void ChangeReferenceStatus(int refId, bool newStatus)
        {
            string updte;
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            updte = string.Format("UPDATE Reference SET isContacted = {0} WHERE RefID = {1}",
                Convert.ToInt32(newStatus).ToString(),refId);
            SqlCommand cmd = new SqlCommand(updte, conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            update();
        }
    }
}